﻿#region License
/* Copyright (C) 2009 Tim Coulter
 * 
 * This file is part of ExtremeML.
 * 
 * ExtremeML is free software: you can redistribute it and/or
 * modify it under the terms of the GNU General Public License
 * as published by the Free Software Foundation either version
 * 2 of the License, or (at your option) any later version.
 * 
 * ExtremeML is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty
 * of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See
 * the GNU General Public License for more details.
 * 
 * You should have received a copy of the GNU General Public License
 * along with this program. If not, see http://www.gnu.org/licenses/.
*/
#endregion

using System;
using System.Diagnostics;
using ExtremeML.Packaging;
using ExtremeML.Spreadsheet.Address;
using ExtremeML.Spreadsheet.Data;
using NUnit.Framework;

namespace ExtremeML.Tests
{
	[TestFixture]
	public class ManipulateCellValues
	{
		private readonly Random _random = new Random();
		private readonly string[] _strings = new[] { "one", "two", "three", "four", "five" };

		private readonly Uri[] _uris = new[]
		{
			new Uri("http://www.microsoft.com/"),
			new Uri("http://www.google.com/"),
			new Uri("http://www.yahoo.com"),
			new Uri("http://www.youtube.com"),
			new Uri("http://www.bbc.co.uk/")
		};

		private readonly HyperlinkUri[] _hyperlinkUris = new[]
		{
			new HyperlinkUri("http://www.microsoft.com/", "Microsoft"),
			new HyperlinkUri("http://www.google.com/", "Google"),
			new HyperlinkUri("http://www.yahoo.com", "Yahoo"),
			new HyperlinkUri("http://www.youtube.com", "YouTube"),
			new HyperlinkUri("http://www.bbc.co.uk/", "BBC")
		};

		[Test]
		public void PopulateCellsInSequence()
		{
			using (var package = SpreadsheetDocumentWrapper.Open(Utility.CreateFileFromResource(Utility.EmptyWorkbookTemplateResource)))
			{
				var part = package.WorkbookPart.WorksheetParts["Sheet1"];

				for (var row = 0; row < 10; row++)
				{
					for (var col = 0; col < 10; col++)
					{
						part.Worksheet.SetCellValue(new GridReference(row, col), _random.Next(100));
					}
				}

				Assert.AreEqual(0, package.WorkbookPart.SharedStringTablePart.SharedStringTable.SharedStringItems.Count, "Shared string count");
				Assert.AreEqual(10, part.Worksheet.SheetData.Rows.Count, "Row count");

				foreach (var row in part.Worksheet.SheetData.Rows)
				{
					Assert.AreEqual(row.Cells.Count, 10, "Row cell count");

					foreach (var cell in row.Cells)
					{
						var rowIndex = cell.CellAddress.RowIndex.Value;
						var columnIndex = cell.CellAddress.ColumnIndex.Value;
						var value = part.Worksheet.GetCellValue<int?>(new GridReference(rowIndex, columnIndex));
						Assert.That(value.HasValue, "Cell value is null");
						Assert.GreaterOrEqual(value.Value, 0, "Cell value");
						Assert.LessOrEqual(value.Value, 100, "Cell value");
					}
				}
			}
		}

		[Test]
		public void PopulateCellsInReverseSequence()
		{
			using (var package = SpreadsheetDocumentWrapper.Open(Utility.CreateFileFromResource(Utility.EmptyWorkbookTemplateResource)))
			{
				var part = package.WorkbookPart.WorksheetParts["Sheet1"];

				for (var col = 9; col > -1; col--)
				{
					for (var row = 9; row > -1; row--)
					{
						part.Worksheet.SetCellValue(new GridReference(row, col), _random.Next(100));
					}
				}

				Assert.AreEqual(package.WorkbookPart.SharedStringTablePart.SharedStringTable.SharedStringItems.Count, 0, "Shared string count");
				Assert.AreEqual(part.Worksheet.SheetData.Rows.Count, 10, "Row count");

				foreach (var row in part.Worksheet.SheetData.Rows)
				{
					Assert.AreEqual(row.Cells.Count, 10, "Row cell count");

					foreach (var cell in row.Cells)
					{
						var rowIndex = cell.CellAddress.RowIndex.Value;
						var columnIndex = cell.CellAddress.ColumnIndex.Value;
						var value = part.Worksheet.GetCellValue<int?>(new GridReference(rowIndex, columnIndex));
						Assert.That(value.HasValue, "Cell value is null");
						Assert.GreaterOrEqual(value.Value, 0, "Cell value");
						Assert.LessOrEqual(value.Value, 100, "Cell value");
					}
				}
			}
		}

		[Test]
		public void VerifyIntegerStorageAndRetrieval()
		{
			using (var package = SpreadsheetDocumentWrapper.Open(Utility.CreateFileFromResource(Utility.EmptyWorkbookTemplateResource)))
			{
				var part = package.WorkbookPart.WorksheetParts["Sheet1"];

				for (var i = 0; i < 100; i++)
				{
					var stored = _random.Next(100);
					var target = new GridReference(0, 0);
					part.Worksheet.SetCellValue(target, stored);
					var retrieved = part.Worksheet.GetCellValue<int?>(target);

					Assert.That(retrieved.HasValue, "Cell value is null");
					Assert.AreEqual(stored, retrieved.Value, "Cell value");
				}

				var empty = part.Worksheet.GetCellValue<int?>(new GridReference(1, 1));
				Assert.IsFalse(empty.HasValue, "Cell value is null");
			}
		}

		[Test]
		public void VerifyDecimalStorageAndRetrieval()
		{
			using (var package = SpreadsheetDocumentWrapper.Open(Utility.CreateFileFromResource(Utility.EmptyWorkbookTemplateResource)))
			{
				var part = package.WorkbookPart.WorksheetParts["Sheet1"];

				for (var i = 0; i < 100; i++)
				{
					var stored = _random.Next(100) / 6.31m;
					var target = new GridReference(0, 0);
					part.Worksheet.SetCellValue(target, stored);
					var retrieved = part.Worksheet.GetCellValue<decimal?>(target);

					Assert.That(retrieved.HasValue, "Cell value is null");
					// String values are compared to eliminate occasional rounding errors.
					Assert.AreEqual(stored.ToString(), retrieved.Value.ToString(), "Cell value");
				}

				var empty = part.Worksheet.GetCellValue<decimal?>(new GridReference(1, 1));
				Assert.IsFalse(empty.HasValue, "Cell value is null");
			}
		}

		[Test]
		public void VerifySingleStorageAndRetrieval()
		{
			using (var package = SpreadsheetDocumentWrapper.Open(Utility.CreateFileFromResource(Utility.EmptyWorkbookTemplateResource)))
			{
				var part = package.WorkbookPart.WorksheetParts["Sheet1"];

				for (var i = 0; i < 100; i++)
				{
					var stored = _random.Next(100) / 6.31f;
					var target = new GridReference(0, 0);
					part.Worksheet.SetCellValue(target, stored);
					var retrieved = part.Worksheet.GetCellValue<Single?>(target);

					Assert.That(retrieved.HasValue, "Cell value is null");
					// String values are compared to eliminate occasional rounding errors.
					Assert.AreEqual(stored.ToString(), retrieved.Value.ToString(), "Cell value");
				}

				var empty = part.Worksheet.GetCellValue<Single?>(new GridReference(1, 1));
				Assert.IsFalse(empty.HasValue, "Cell value is null");
			}
		}

		[Test]
		public void VerifyDoubleStorageAndRetrieval()
		{
			using (var package = SpreadsheetDocumentWrapper.Open(Utility.CreateFileFromResource(Utility.EmptyWorkbookTemplateResource)))
			{
				var part = package.WorkbookPart.WorksheetParts["Sheet1"];

				for (var i = 0; i < 100; i++)
				{
					var stored = _random.Next(100) / 100006.31d;
					var target = new GridReference(0, 0);
					part.Worksheet.SetCellValue(target, stored);
					var retrieved = part.Worksheet.GetCellValue<double?>(target);

					Assert.That(retrieved.HasValue, "Cell value is null");
					// String values are compared to eliminate occasional rounding errors.
					Assert.AreEqual(stored.ToString(), retrieved.Value.ToString(), "Cell value");
				}

				var empty = part.Worksheet.GetCellValue<double?>(new GridReference(1, 1));
				Assert.IsFalse(empty.HasValue, "Cell value is null");
			}
		}

		[Test]
		public void VerifyDoubleStorageAndRetrievalExponentFormat()
		{
			using (var package = SpreadsheetDocumentWrapper.Open(Utility.CreateFileFromResource(Utility.EmptyWorkbookTemplateResource)))
			{
				var part = package.WorkbookPart.WorksheetParts["Sheet1"];

				var values = new[]
				{
					7.45E05,
					7.45E-05,
					-7.45E05,
					-7.45E-05
				};

				foreach (var value in values)
				{
					var target = new GridReference(0, 0);
					part.Worksheet.SetCellValue(target, value);
					var retrieved = part.Worksheet.GetCellValue<double?>(target);

					Assert.That(retrieved.HasValue, "Cell value is null");
					Assert.AreEqual(value, retrieved.Value, "Cell value");
				}
			}
		}

		[Test]
		public void VerifyDateStorageAndRetrieval()
		{
			using (var package = SpreadsheetDocumentWrapper.Open(Utility.CreateFileFromResource(Utility.EmptyWorkbookTemplateResource)))
			{
				var part = package.WorkbookPart.WorksheetParts["Sheet1"];

				for (var i = 0; i < 100; i++)
				{
					var stored = DateTime.Now.AddDays(_random.Next(1000));
					var target = new GridReference(0, 0);
					part.Worksheet.SetCellValue(target, stored);
					var retrieved = part.Worksheet.GetCellValue<DateTime?>(target);

					Assert.That(retrieved.HasValue, "Cell value is null");
					// String values are compared to eliminate occasional rounding errors.
					Assert.AreEqual(stored.ToLongDateString(), retrieved.Value.ToLongDateString(), "Cell value");
				}

				var empty = part.Worksheet.GetCellValue<DateTime?>(new GridReference(1, 1));
				Assert.IsFalse(empty.HasValue, "Cell value is null");
			}
		}

		[Test]
		public void VerifyTimeStorageAndRetrieval()
		{
			using (var package = SpreadsheetDocumentWrapper.Open(Utility.CreateFileFromResource(Utility.EmptyWorkbookTemplateResource)))
			{
				var part = package.WorkbookPart.WorksheetParts["Sheet1"];

				for (var i = 0; i < 100; i++)
				{
					var stored = new TimeSpan(0, _random.Next(1000), 0);
					var target = new GridReference(0, 0);
					part.Worksheet.SetCellValue(target, stored);
					var retrieved = part.Worksheet.GetCellValue<TimeSpan?>(target);

					Assert.That(retrieved.HasValue, "Cell value is null");
					// String values are compared to eliminate occasional rounding errors.
					Assert.AreEqual(stored.ToString(), retrieved.ToString(), "Cell value");
				}

				var empty = part.Worksheet.GetCellValue<TimeSpan?>(new GridReference(1, 1));
				Assert.IsFalse(empty.HasValue, "Cell value is null");
			}
		}

		[Test]
		public void VerifyBoolStorageAndRetrieval()
		{
			using (var package = SpreadsheetDocumentWrapper.Open(Utility.CreateFileFromResource(Utility.EmptyWorkbookTemplateResource)))
			{
				var part = package.WorkbookPart.WorksheetParts["Sheet1"];

				for (var i = 0; i < 100; i++)
				{
					var number = _random.Next(1000);
					var stored = number / 2m == Math.Round(number / 2m);
					var target = new GridReference(0, 0);
					part.Worksheet.SetCellValue(target, stored);
					var retrieved = part.Worksheet.GetCellValue<bool?>(target);

					Assert.That(retrieved.HasValue, "Cell has value");
					// String values are compared to eliminate occasional rounding errors.
					Assert.AreEqual(stored, retrieved, "Cell value");
				}

				var empty = part.Worksheet.GetCellValue<bool?>(new GridReference(1, 1));
				Assert.IsFalse(empty.HasValue, "Cell value is null");
			}
		}

		[Test]
		public void VerifyStringStorageAndRetrieval()
		{
			using (var package = SpreadsheetDocumentWrapper.Open(Utility.CreateFileFromResource(Utility.EmptyWorkbookTemplateResource)))
			{
				var part = package.WorkbookPart.WorksheetParts["Sheet1"];

				for (var i = 0; i < 100; i++)
				{
					var stored = _strings[_random.Next(5)];
					var target = new GridReference(0, 0);
					part.Worksheet.SetCellValue(target, stored);
					var retrieved = part.Worksheet.GetCellValue<string>(target);

					Assert.NotNull(retrieved, "Cell value is null");
					// String values are compared to eliminate occasional rounding errors.
					Assert.AreEqual(stored, retrieved, "Cell value");
				}

				var empty = part.Worksheet.GetCellValue<string>(new GridReference(1, 1));
				Assert.IsNull(empty, "Cell value is null");
			}
		}

		[Test]
		public void VerifySharedStringTableUsage()
		{
			using (var package = SpreadsheetDocumentWrapper.Open(Utility.CreateFileFromResource(Utility.EmptyWorkbookTemplateResource)))
			{
				var part = package.WorkbookPart.WorksheetParts["Sheet1"];
				var table = package.WorkbookPart.SharedStringTablePart.SharedStringTable;

				Assert.AreEqual(0, table.SharedStringItems.Count, "Shared string count.");
	
				// Setting 100 cells to random string values should consume all five sample strings
				for (var row = 0; row < 10; row++)
				{
					for (var col = 0; col < 10; col++)
					{
						part.Worksheet.SetCellValue(new GridReference(row, col), _strings[_random.Next(5)]);
					}
				}

				Assert.AreEqual(5, table.SharedStringItems.Count, "Shared string count.");

				// Setting 100 cells to a fixed value should release all five sample strings
				for (var row = 0; row < 10; row++)
				{
					for (var col = 0; col < 10; col++)
					{
						part.Worksheet.SetCellValue(new GridReference(row, col), "fixed string");
					}
				}

				Assert.AreEqual(1, table.SharedStringItems.Count, "Shared string count.");
			}
		}

		[Test]
		public void VerifyUriStorageAndRetrieval()
		{
			using (var package = SpreadsheetDocumentWrapper.Open(Utility.CreateFileFromResource(Utility.EmptyWorkbookTemplateResource)))
			{
				var part = package.WorkbookPart.WorksheetParts["Sheet1"];

				for (var i = 0; i < 100; i++)
				{
					var stored = _uris[_random.Next(5)];
					var target = new GridReference(0, 0);
					part.Worksheet.SetCellValue(target, stored);
					var retrieved = part.Worksheet.GetCellValue<Uri>(target);

					Assert.NotNull(retrieved, "Cell value is null");
					Assert.AreEqual(stored, retrieved, "Cell value");
				}

				var empty = part.Worksheet.GetCellValue<Uri>(new GridReference(1, 1));
				Assert.IsNull(empty, "Cell value is null");
			}
		}

		[Test]
		public void VerifyHyperlinkUriStorageAndRetrieval()
		{
			using (var package = SpreadsheetDocumentWrapper.Open(Utility.CreateFileFromResource(Utility.EmptyWorkbookTemplateResource)))
			{
				var part = package.WorkbookPart.WorksheetParts["Sheet1"];

				for (var i = 0; i < 100; i++)
				{
					var stored = _hyperlinkUris[_random.Next(5)];
					var target = new GridReference(0, 0);
					part.Worksheet.SetCellValue(target, stored);
					var retrieved = part.Worksheet.GetCellValue<HyperlinkUri>(target);

					Assert.NotNull(retrieved, "Cell value is null");
					Assert.AreEqual(stored.AbsoluteUri, retrieved.AbsoluteUri, "Cell Uri value");
					Assert.AreEqual(stored.Text, retrieved.Text, "Cell Uri value");
				}

				var empty = part.Worksheet.GetCellValue<Uri>(new GridReference(1, 1));
				Assert.IsNull(empty, "Cell value is null");
			}
		}

		[Test]
		public void VerifyImplicitHyperlinks()
		{
			using (var package = SpreadsheetDocumentWrapper.Open(Utility.CreateFileFromResource(Utility.EmptyWorkbookTemplateResource)))
			{
				var part = package.WorkbookPart.WorksheetParts["Sheet1"];

				// Setting 100 cells to random uri values should consume all five sample uris
				for (var row = 0; row < 10; row++)
				{
					for (var col = 0; col < 10; col++)
					{
						part.Worksheet.SetCellValue(new GridReference(row, col), _uris[_random.Next(5)]);
					}
				}

				Assert.AreEqual(100, part.Worksheet.Hyperlinks.Hyperlinks.Count, "Hyperlink count.");

				// Setting 100 cells to a fixed value should release all hyperlinks
				for (var row = 0; row < 10; row++)
				{
					for (var col = 0; col < 10; col++)
					{
						part.Worksheet.SetCellValue(new GridReference(row, col), "fixed string");
					}
				}

				Assert.AreEqual(0, part.Worksheet.Hyperlinks.Hyperlinks.Count, "Hyperlink count.");
			}
		}

		[Test]
		public void VerifyExplicitHyperlinks()
		{
			using (var package = SpreadsheetDocumentWrapper.Open(Utility.CreateFileFromResource(Utility.EmptyWorkbookTemplateResource)))
			{
				var part = package.WorkbookPart.WorksheetParts["Sheet1"];

				// Setting 100 cells to random hyperlink values should consume all five sample data items
				for (var row = 0; row < 10; row++)
				{
					for (var col = 0; col < 10; col++)
					{
						part.Worksheet.SetCellValue(new GridReference(row, col), _hyperlinkUris[_random.Next(5)]);
					}
				}

				Assert.AreEqual(100, part.Worksheet.Hyperlinks.Hyperlinks.Count, "Hyperlink count.");

				// Setting 100 cells to a fixed value should release all hyperlinks
				for (var row = 0; row < 10; row++)
				{
					for (var col = 0; col < 10; col++)
					{
						part.Worksheet.SetCellValue(new GridReference(row, col), "fixed string");
					}
				}

				Assert.AreEqual(0, part.Worksheet.Hyperlinks.Hyperlinks.Count, "Hyperlink count.");
			}
		}
	}
}
